﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Anke.SHManage.Model;
using Anke.SHManage.IDAL;
using System.Data;
using Anke.SHManage.Utility;


namespace Anke.SHManage.MSSQLDAL
{
    public partial class I_BalanceDALExt
    {
        IDALContext dalContext = new DALContextFactory().GetDALContext();
        AKV7ManageEntities db = DBContextFactory.GetDBContext() as AKV7ManageEntities;

        public object GetBalanceList(int page, int rows, string monthtime, List<string> Type, ref int rowCounts)
        {
            var q = (from b in db.I_BalanceList
                     select new
                     {
                         MaterialTypeID = b.MaterialTypeID,
                         ReportMonth = b.ReportMonth,                     
                         Name = b.TDictionary.Name

                     });
            if (!string.IsNullOrEmpty(monthtime))
            {
                q = q.Where(a => a.ReportMonth == monthtime);
               
            }
            //if (!string.IsNullOrEmpty(Type))
            //{
            //    q = q.Where(a => a.MaterialTypeID == Type);
            //}
            if (Type.Count > 0) //add2021-10-15 plq 医保上线移植
            {
                q = q.Where(a => Type.Contains(a.MaterialTypeID));
            }
            rowCounts = q.Count();
            var r = q.OrderByDescending(a =>a.ReportMonth).Skip((page - 1) * rows).Take(rows).ToList();
            return r;
        }
        /// <summary>
        /// 根据物资类型查询产生的最大生成统计报表的时间
        /// </summary>
        /// <param name="MTypeID"></param>
        /// <returns></returns>
        public string GetBalanceMax(string MTypeID)
        {
            try
            {
                string max = (from b in db.I_BalanceList
                              where b.MaterialTypeID == MTypeID 
                              orderby b.ReportMonth descending
                              select b.ReportMonth).First()
                              ;
                return max;
            }
            catch
            {
                return "";
            }

        }
        //取最大生成统计报表的时间
        public string GetBalanceMaxMonth()
        {
            try
            {
                string max = (from b in db.I_BalanceList                           
                              orderby b.ReportMonth descending
                              select b.ReportMonth).First()
                              ;
                return max;
            }
            catch
            {
                return "";
            }

        }
        /// <summary>
        /// 插入I_Balance数据
        /// </summary>
        /// <param name="list"></param>
        /// <param name="errorMsg"></param>
        /// <returns></returns>
        public bool AddBalance(List<I_Balance> list,I_BalanceList blinfo,ref string errorMsg)
        {
            using (var tran = db.Database.BeginTransaction())
            {

                try
                {
                    db.I_BalanceList.Add(blinfo);
                    db.I_Balance.AddRange(list);
                    db.SaveChanges();
                    tran.Commit(); //提交事务
                    return true;
                }
                catch (Exception ex)
                {
                    if (tran != null)
                        tran.Rollback();  //回滚事务
                    errorMsg = "存储数据错误：" + ex.Message;
                    LogUtility.Error("I_BalanceDALExt/AddBalance", ex.Message); 
                    return false;
                }
            }
        }
        /// <summary>
        /// 根据时间段查询财务统计报表
        /// </summary>
        /// <param name="reportTime"></param>
        /// <param name="MTypeID"></param>
        /// <param name="errorMsg"></param>
        /// <returns></returns>
        public List<I_Balance> GetI_BalanceNewList(string reportMonth, string lastReportMonth, string MTypeID, ref string errorMsg)
    {
            try
            {
                StringBuilder sbSQL = new StringBuilder();//sql语句
                sbSQL.Append("declare @LastReportMonth varchar(50) ");
                sbSQL.Append("set @LastReportMonth='" + lastReportMonth + "' ");
                sbSQL.Append(@" SELECT im.MCode,iin.MaterialID,im.Name,ReportMonth='" + reportMonth + @"'
                                ,(ISNULL((SELECT SurplusCounts FROM I_Balance where MaterialID=iin.MaterialID and ReportMonth=@LastReportMonth),0)) as 上月数量
                                ,(ISNULL((SELECT SurplusPrice FROM I_Balance where MaterialID=iin.MaterialID and ReportMonth=@LastReportMonth),0)) as 上月价格
                                ,sum(case when iin.Is_Entry=1 then iin.ChangeSurplus else 0 end) as 进货数量
                                ,sum(case when iin.Is_Entry=1 then iin.SurplusPrice else 0 end) as 进货价格
                                ,sum(case when iin.Is_Entry=0 then iin.ChangeSurplus else 0 end) as 出货数量
                                 ,cast(round(
								((sum(case when iin.Is_Entry=1 then iin.SurplusPrice else 0 end)
								+(ISNULL((SELECT SurplusPrice FROM I_Balance where MaterialID=iin.MaterialID and ReportMonth=@LastReportMonth),0)))
								/case when isnull((sum(case when iin.Is_Entry=1 then iin.ChangeSurplus else 0 end))
								+(ISNULL((SELECT SurplusCounts FROM I_Balance where MaterialID=iin.MaterialID and ReportMonth=@LastReportMonth),0)),0)=0 
								then 1 else  isnull((sum(case when iin.Is_Entry=1 then iin.ChangeSurplus else 0 end))
								+(ISNULL((SELECT SurplusCounts FROM I_Balance where MaterialID=iin.MaterialID and ReportMonth=@LastReportMonth),0)),0) end
                                ),2)   as   numeric(10,2))
								*sum(case when iin.Is_Entry=0 then iin.ChangeSurplus else 0 end)
								 as 出货价格
                               ,sum(iin.ChangeSurplus)+
                               (ISNULL((SELECT SurplusCounts FROM I_Balance where MaterialID=iin.MaterialID and ReportMonth=@LastReportMonth),0)) as 结余数量
                               ,convert(numeric(8,2),isnull((sum(case when iin.Is_Entry=1 then iin.SurplusPrice else 0 end)+
                                cast(round(
								((sum(case when iin.Is_Entry=1 then iin.SurplusPrice else 0 end)
								+(ISNULL((SELECT SurplusPrice FROM I_Balance where MaterialID=iin.MaterialID and ReportMonth=@LastReportMonth),0)))
								/case when isnull((sum(case when iin.Is_Entry=1  then iin.ChangeSurplus else 0 end)
								+(ISNULL((SELECT SurplusCounts FROM I_Balance where MaterialID=iin.MaterialID and ReportMonth=@LastReportMonth),0))),0)=0
								then 1 else isnull((sum(case when iin.Is_Entry=1  then iin.ChangeSurplus else 0 end)
								+(ISNULL((SELECT SurplusCounts FROM I_Balance where MaterialID=iin.MaterialID and ReportMonth=@LastReportMonth),0))),0) end
                               ),2)   as   numeric(10,2))
								*sum(case when iin.Is_Entry=0 then iin.ChangeSurplus else 0 end)
								
                               +(ISNULL((SELECT SurplusPrice FROM I_Balance where MaterialID=iin.MaterialID and ReportMonth=@LastReportMonth),0))),0))
                                as 结余价格                      
                    FROM I_InventoryRecord iin
                    left join I_Material im on iin.MaterialID = im.ID
                    where  iin.ReportMonth = '" + reportMonth+@"'  ");
                if (MTypeID.Length > 0) //add2021-10-15 plq 医保上线移植
                {
                    sbSQL.Append(@" and im.MTypeID in ('" + MTypeID + "') ");
                }
//                sbSQL.Append(@"
//                    group by iin.MaterialID,im.Name 
//					union all
//				    select ID,Name,ReportMonth='" + reportMonth + @"',上月数量=b.SurplusCounts,上月价格=b.SurplusPrice,进货数量=0,进货价格=0,
//                           出货数量=0,出货价格=0,结余数量=b.SurplusCounts,结余价格=b.SurplusPrice
//				    from I_Balance b
//					left join I_Material m on m.ID=b.MaterialID  
//				    where ID not in(select MaterialID  from I_InventoryRecord where ReportMonth='" + reportMonth + "') and IsActive=1 and b.ReportMonth=@LastReportMonth");
//                if (MTypeID == "MaterialType-9998")
//                { sbSQL.Append(@" and MTypeID in ('MaterialType-13','MaterialType-14','MaterialType-15','MaterialType-16','MaterialType-26')"); }
//                if (MTypeID == "MaterialType-9999")
//                {
//                    sbSQL.Append(@" and MTypeID in ('MaterialType-17','MaterialType-18','MaterialType-19','MaterialType-20','MaterialType-21'
//                                                   ,'MaterialType-22','MaterialType-23','MaterialType-24','MaterialType-25')");
//                }
                sbSQL.Append(@"
                    group by iin.MaterialID,im.Name,im.MCode
					union all
				    select m.MCode,ID,Name,ReportMonth='"+reportMonth+@"',上月数量=b.SurplusCounts,上月价格=b.SurplusPrice,进货数量=0,进货价格=0,
                           出货数量=0,出货价格=0,结余数量=b.SurplusCounts,结余价格=b.SurplusPrice
				    from I_Balance b
					left join I_Material m on m.ID=b.MaterialID  
				    where ID not in(select MaterialID  from I_InventoryRecord where ReportMonth='" + reportMonth + "') and IsActive=1 and b.ReportMonth=@LastReportMonth");
                if (MTypeID.Length > 0) //add2021-10-15 plq 医保上线移植
                {
                    sbSQL.Append(@" and MTypeID in ('" + MTypeID + "') ");
                }
               
                DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.MainConnectionString, CommandType.Text, sbSQL.ToString(), null);
                List<I_Balance> list = new List<I_Balance>();
                if (ds.Tables[0].Rows.Count > 0)
                {
                    foreach (DataRow dr in ds.Tables[0].Rows)
                    {
                        I_Balance info = new I_Balance();
                        info.MCode = dr["MCode"].ToString();
                        info.MaterialID = Convert.ToInt32(dr["MaterialID"]);
                        info.MName = dr["Name"].ToString();
                        info.ReportMonth = reportMonth.ToString();
                        info.BeginningCounts = Convert.ToDouble(dr["上月数量"]);
                        info.BeginningPrice = Convert.ToDecimal(dr["上月价格"]);
                        info.IncomeCounts = Convert.ToDouble(dr["进货数量"]);
                        info.IncomePrice = Convert.ToDecimal(dr["进货价格"]);
                        info.PayCounts = Convert.ToDouble(dr["出货数量"]);
                        info.PayPrice = Convert.ToDecimal(dr["出货价格"]);
                        info.UpdataPrice = 0;
                        info.SurplusCounts = Convert.ToDouble(dr["结余数量"]);
                        info.SurplusPrice = Convert.ToDecimal(dr["结余价格"]);
                        info.SurplusTime = Convert.ToDateTime(DateTime.Now);
                        list.Add(info);
                    }
                }               
                return list;          
            }
            catch (Exception ex)
            {
                errorMsg = "系统错误：" + ex.Message;
                LogUtility.Error("I_BalanceDALExt/GetI_BalanceNewList", ex.Message);
                return null;
            }
        }


        public bool DeleteBalance(string ReportTime, string TypeList, string MaterialTypeID)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(@" delete I_BalanceList where ReportMonth='" + ReportTime + "' and MaterialTypeID= '" + MaterialTypeID + "'; delete I_Balance  where ReportMonth ='" + ReportTime + "' ");
            if (TypeList.Length > 0)
            {
                sb.Append(@" and MaterialID in (select ID from I_Material im  where im.MTypeID in ('" + TypeList + "') )");
            }
            try
            {
                int num = SqlHelper.ExecuteNonQuery(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null);
                if (num > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception ex)
            {
                LogUtility.Error("I_BalanceDALExt/DeleteBalance", ex.Message);
                return false;
            }
        }

    }
}
